﻿using DataAchive.Common;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;

namespace DataAchive.Helper
{
    public class SqlTestHelper
    {
        public static bool HasConnected( string connstr)
        {            
           
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    cmd.Connection = connection;                   

                    cmd.Connection = connection;

                    return true;
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("HasConnected测试连接异常：" + e.Message);
                   return false;
                }

            }

        }

        //忽略 site info.
       static string DataCountStr = "SELECT COUNT(*) FROM NG";
        static string DataEquipmentStr = "SELECT COUNT(*) FROM NG LEFT JOIN SITE ON NG.`site_id`=SITE.`id`";
        public static int HasNumberData(WhereQ whereq, string connstr)
        {
            string wherestr = ReportHelper.GetWhereCause(whereq);            
            string cmdText = DataCountStr + wherestr ;
            if (wherestr.IndexOf("equipment_id", StringComparison.OrdinalIgnoreCase) > -1)
            {
                cmdText = DataEquipmentStr + wherestr;
            }
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                   
                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                   object result = cmd.ExecuteScalar();
                    if (result!=null && result != DBNull.Value)
                    {
                        return Convert.ToInt32(result);
                    }
                    return 0;

                   
                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("HasNumberData异常：" + e.Message);                    
                    return 0;
                }

            }

        }



        //忽略 site info.
        static string DataFanXiuStr = "SELECT COUNT(*) FROM NG  WHERE ng.`checked`>0 ";
        static string DataFanXiuEquipmentStr = "SELECT COUNT(*) FROM NG LEFT JOIN SITE ON NG.`site_id`=SITE.`id` WHERE ng.`checked`>0 ";
       
        public static int HasFanXiuNumberData(WhereQ whereq, string connstr)
        {
            string wherestr = ReportHelper.GetWhereCause(whereq);
            wherestr=Regex.Replace(wherestr, "where", " and ", RegexOptions.IgnoreCase);

            string cmdText = DataFanXiuStr + wherestr;
            if (wherestr.IndexOf("equipment_id", StringComparison.OrdinalIgnoreCase) > -1)
            {
                cmdText = DataFanXiuEquipmentStr + wherestr;
            }
            using (MySqlConnection connection = new MySqlConnection(connstr))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    cmd.Connection = connection;
                    cmd.CommandText = cmdText;

                    object result = cmd.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        return Convert.ToInt32(result);
                    }
                    return 0;


                }
                catch (Exception e)
                {
                    SoftCommon.Log.Write("HasNumberData异常：" + e.Message);
                    return 0;
                }

            }

        }
    }
}
